package com.example.servlet;

import com.example.util.DBUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

@WebServlet("/export")
public class ExportServlet extends HttpServlet {
    
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
            throws ServletException, IOException {
        String type = request.getParameter("type");
        
        if ("users".equals(type)) {
            exportUsers(response);
        } else if ("orders".equals(type)) {
            exportOrders(response);
        } else if ("feedback".equals(type)) {
            exportFeedback(response);
        } else if ("books".equals(type)) {
            exportBooks(response);
        } else {
            response.sendRedirect("admin-dashboard.jsp");
        }
    }
    
    private void exportUsers(HttpServletResponse response) throws IOException {
        response.setContentType("text/csv;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=users_" + 
                new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
        
        try (PrintWriter writer = response.getWriter();
             Connection conn = DBUtil.getConnection()) {
            
            // 写入CSV头部
            writer.println("用户ID,用户名,邮箱,电话,地址,是否管理员,状态,注册时间,最后登录时间");
            
            String sql = "SELECT * FROM users ORDER BY register_time DESC";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                writer.printf("%d,%s,%s,%s,%s,%s,%s,%s,%s%n",
                        rs.getInt("id"),
                        escapeCsv(rs.getString("username")),
                        escapeCsv(rs.getString("email")),
                        escapeCsv(rs.getString("phone")),
                        escapeCsv(rs.getString("address")),
                        rs.getBoolean("is_admin") ? "是" : "否",
                        rs.getString("status"),
                        rs.getTimestamp("register_time"),
                        rs.getTimestamp("last_login_time")
                );
            }
            
            // 记录导出日志
            logExport("users", "users_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
            
        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(50, "导出用户数据失败: " + e.getMessage());
        }
    }
    
    private void exportOrders(HttpServletResponse response) throws IOException {
        response.setContentType("text/csv;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=orders_" + 
                new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
        
        try (PrintWriter writer = response.getWriter();
             Connection conn = DBUtil.getConnection()) {
            
            // 写入CSV头部
            writer.println("订单ID,用户ID,图书ID,数量,总价,状态,收货地址,联系电话,下单时间,备注");
            
            String sql = "SELECT o.*, u.username, b.name as book_name FROM orders o JOIN users u ON o.user_id = u.id JOIN books b ON o.book_id = b.id ORDER BY o.order_time DESC";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                writer.printf("%d,%d,%d,%d,%.2f,%s,%s,%s,%s,%s%n",
                        rs.getInt("id"),
                        rs.getInt("user_id"),
                        rs.getInt("book_id"),
                        rs.getInt("quantity"),
                        rs.getDouble("total_price"),
                        rs.getString("status"),
                        escapeCsv(rs.getString("shipping_address")),
                        escapeCsv(rs.getString("contact_phone")),
                        rs.getTimestamp("order_time"),
                        escapeCsv(rs.getString("notes"))
                );
            }
            
            // 记录导出日志
            logExport("orders", "orders_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
            
        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(50, "导出订单数据失败: " + e.getMessage());
        }
    }
    
    private void exportFeedback(HttpServletResponse response) throws IOException {
        response.setContentType("text/csv;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=feedback_" + 
                new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
        
        try (PrintWriter writer = response.getWriter();
             Connection conn = DBUtil.getConnection()) {
            
            // 写入CSV头部
            writer.println("反馈ID,用户ID,用户名,内容,回复,状态,创建时间,回复时间");
            
            String sql = "SELECT f.*, u.username FROM feedback f LEFT JOIN users u ON f.user_id = u.id ORDER BY f.create_time DESC";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                writer.printf("%d,%d,%s,%s,%s,%s,%s,%s%n",
                        rs.getInt("id"),
                        rs.getInt("user_id"),
                        escapeCsv(rs.getString("username")),
                        escapeCsv(rs.getString("content")),
                        escapeCsv(rs.getString("reply")),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getTimestamp("reply_time")
                );
            }
            
            // 记录导出日志
            logExport("feedback", "feedback_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
            
        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(50, "导出反馈数据失败: " + e.getMessage());
        }
    }
    
    private void exportBooks(HttpServletResponse response) throws IOException {
        response.setContentType("text/csv;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=books_" + 
                new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
        
        try (PrintWriter writer = response.getWriter();
             Connection conn = DBUtil.getConnection()) {
            
            // 写入CSV头部
            writer.println("图书ID,标题,描述,价格,库存,销量,浏览量,评分,分类,出版社,作者");
            
            String sql = "SELECT * FROM books ORDER BY id";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            
            while (rs.next()) {
                writer.printf("%d,%s,%s,%.2f,%d,%d,%d,%.2f,%s,%s,%s%n",
                        rs.getInt("id"),
                        escapeCsv(rs.getString("title")),
                        escapeCsv(rs.getString("description")),
                        rs.getDouble("price"),
                        rs.getInt("stock"),
                        rs.getInt("sales"),
                        rs.getInt("views"),
                        rs.getDouble("rating"),
                        escapeCsv(rs.getString("category")),
                        escapeCsv(rs.getString("publisher")),
                        escapeCsv(rs.getString("author"))
                );
            }
            
            // 记录导出日志
            logExport("books", "books_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".csv");
            
        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(50, "导出图书数据失败: " + e.getMessage());
        }
    }
    
    private String escapeCsv(String value) {
        if (value == null) {
            return "";
        }
        // 如果包含逗号、引号或换行符，需要用引号包围并转义内部引号
        if (value.contains(",") || value.contains("\"") || value.contains("\n")) {
            return "\"" + value.replace("\"", "\"\"") + "\"";
        }
        return value;
    }
    
    private void logExport(String type, String fileName) {
        try (Connection conn = DBUtil.getConnection()) {
            String sql = "INSERT INTO export_logs (export_type, file_name, status) VALUES (?, ?, 'completed')";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, type);
            stmt.setString(2, fileName);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
} 